#coding=utf-8
import unittest
import os
import sys
import time
import datetime
import shutil
import  random
from xlwt import *
import xlrd
from xlutils.copy import copy

class mySqlImport():
    # def importOneSql(self):
    #     excel_path = 'd://'
    #     excel_name_base = 'im_config.sql'
    #     sqlfile = excel_path + excel_name_base
    #     command = 'mysql -h 192.168.32.200 -P 3306 -uroot -pegova cgdb0901 < d:\im_config.sql'
    #     os.system(command)
    #     print 'sql import successful'
    #     return  0
    #
    # def get_file_list(self):
    #
    #     file_name_list = os.listdir('d:/sql')
    #     for i in range(0,len(file_name_list)):
    #         print file_name_list[i]
    #
    #     return  0

    def import_sqls(self):
        base_dir = 'd:/sql/cgdb/'
        bak_dir = 'd:/sql/bak/'
        connectInfor = 'mysql -h 192.168.32.200 -P 3306 -uroot -pegova'
        databases = 'cgdb0612'
        sql_file_list = os.listdir(base_dir)
        for i in range(0,len(sql_file_list)):
            sql_file = base_dir + sql_file_list[i]
            sql_file_bak = bak_dir + sql_file_list[i]
            command = connectInfor + ' '+ databases + ' < ' + sql_file
            sql_size = os.path.getsize(sql_file) / 1024 / 1024
            # StartTime = time.localtime(time.time())
            StartTime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
            start = datetime.datetime.now()
            os.system(command)
            end = datetime.datetime.now()
            endTime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
            useTime = (end-start).seconds
            shutil.move(sql_file,sql_file_bak)
            # shutil.copy2(sql_file,'D://test.sql')
            # os.remove(sql_file)
            # print 'sql_size = '+ str(sql_size)
            # print 'command = ' + command
            # print 'sql_size = '+ str(sql_size) + ';startTime=' + StartTime + ';endtime = '+endTime
            print 'sqlname:'+sql_file_list[i]+'; size= ' + str(sql_size) + 'M; useTime=' + str(useTime) + 's '
        return 0

if __name__ == '__main__':
    test = mySqlImport()
    # # test.create_excel()
    # testValue = [10,11,12,13]
    # test.excel_context_op('d://test-2017-04-17276.532827812.csv',0,testValue)
    test.import_sqls()
    unittest.main()
